package com.grom.excel;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import com.grom.po.ContactInfo;
import com.grom.po.Customer;
import com.grom.service.CustomerService;
import com.grom.service.GcmsUserService;
import com.grom.util.ExcelReader;
import com.grom.util.JsonUtil;
import com.holder.log.ConsoleLog;

public class ExcelValueBinder {
	private Sheet sheet;

	private String cusType;

	private int num = 0;

	public ExcelValueBinder(Sheet sheet, String cusType) {
		this.sheet = sheet;
		this.cusType = cusType;
		num = sheet.getPhysicalNumberOfRows();
		ConsoleLog.debug("| ExcelValueBinder | num |" + num);
	}

	public void doBind() {
		for (int i = 1; i < num; i++) {
			Customer customer = new Customer();
			Row row = sheet.getRow(i);
			if (row == null || row.getCell(0) == null) {
				continue;
			}
			String userName = getCellContent(row.getCell(13)).toString();
			if (userName == null || userName.trim().equals("")) {
				continue;
			}
			String userId = GcmsUserService.instance
					.getUserByUserName(userName).getID();
			if (userId == null || userId.trim().equals("")) {
				continue;
			}
			customer.setCustomerName(getCellContent(row.getCell(0)).toString());
			customer.setCusType(cusType);
			customer.setVisitedAddress(getCellContent(row.getCell(4))
					.toString());
			customer.setUserId(userId);
			ContactInfo cusInfo = new ContactInfo();
			cusInfo.setContactPerson(getCellContent(row.getCell(6)).toString());
			cusInfo.setContactPhone(getCellContent(row.getCell(8)).toString());
			List<ContactInfo> lisInfo = new ArrayList<ContactInfo>();
			lisInfo.add(cusInfo);
			CustomerService.instance.add(customer, lisInfo,null);
		}
	}

	public Object getCellContent(Cell cell) {
		if (cell == null) {
			return "";
		}
		FormulaEvaluator evaluator = cell.getSheet().getWorkbook()
				.getCreationHelper().createFormulaEvaluator();
		try {
			if (evaluator.evaluate(cell) == null) {
				return "";
			}
		} catch (Exception e) {
			return cell.getCellFormula();
		}
		switch (evaluator.evaluate(cell).getCellType()) {
		case Cell.CELL_TYPE_STRING:
			return cell.getStringCellValue();
		case Cell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				return cell.getDateCellValue();
			}
			return cell.getNumericCellValue();
		case Cell.CELL_TYPE_FORMULA:
			return cell.getCellFormula();
		case Cell.CELL_TYPE_BOOLEAN:
			return cell.getBooleanCellValue();
		case Cell.CELL_TYPE_BLANK:
			return " ";
		default:
			return "";
		}
	}

	public static void main(String[] args) {
		try {
			ExcelReader reader = new ExcelReader(new File("D:\\客户资料表.xlsx"));
			new ExcelValueBinder(reader.getSheetC(), "C").doBind();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}
